package com.bz.xtcx.manager.mapper.provider;

import com.bz.xtcx.manager.entity.CmsContent;
import com.bz.xtcx.manager.persist.conditions.ContentCondition;
import com.bz.xtcx.manager.utils.WzDateUtil;
import com.bz.xtcx.manager.utils.WzStrUtil;
import com.bz.xtcx.manager.vo.VoQueryCms;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.util.StringUtils;

import java.util.Date;

public class CmsContentProvider {

    public String countByKeyStrOrRecommendProvider(ContentCondition cc) {
        StringBuilder sql = new StringBuilder("select count(DISTINCT a.id) ");
        sql.append("from cms_content a inner join cms_category b on a.categroy_id = b.id ");
        sql.append("where 1 = 1 ");
        if (null != cc.getCheckStatus()) {
            sql.append("and a.check_status = " + cc.getCheckStatus() + " ");
        }
        if (null != cc.getReadable()) {
            sql.append("and a.readable = " + cc.getReadable() + " ");
        }
        if (null != cc.getRecommend()) {
            sql.append("and a.recommend = " + cc.getRecommend() + " ");
        }
        if (WzStrUtil.isNotBlank(cc.getKeyStr())) {
            sql.append(" and ( ");
            sql.append(" a.title like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or a.description like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or a.author like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or a.tags like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or a.content_txt like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or b.name like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or b.tags like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or b.memo like '%" + cc.getKeyStr() + "%' ");
            sql.append(" ) ");
        }
        sql.append(" and a.type_id = 1 ");
        sql.append(" and a.publish_date < '" + WzDateUtil.dt2Str(new Date(), WzDateUtil.DT_FMT_5) + "' ");
        return sql.toString();
    }

    public String countByCategoryCodeProvider(ContentCondition cc) {
        StringBuilder sql = new StringBuilder("select count(DISTINCT a.id) ");
        sql.append("from cms_content a inner join cms_category b on a.categroy_id = b.id ");
        sql.append("where 1 = 1 ");
        if (WzStrUtil.isNotBlank(cc.getCategoryCode())) {
            sql.append("and b.code = '" + cc.getCategoryCode() + "' ");
        }
        if (null != cc.getCheckStatus()) {
            sql.append("and a.check_status = " + cc.getCheckStatus() + " ");
        }
        if (null != cc.getReadable()) {
            sql.append("and a.readable = " + cc.getReadable() + " ");
        }
        if (null != cc.getRecommend()) {
            sql.append("and a.recommend = " + cc.getRecommend() + " ");
        }
        if (WzStrUtil.isNotBlank(cc.getKeyStr())) {
            sql.append(" and ( ");
            sql.append(" a.title like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or a.description like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or a.author like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or a.tags like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or a.content_txt like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or b.name like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or b.tags like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or b.memo like '%" + cc.getKeyStr() + "%' ");
            sql.append(" ) ");
        }
        sql.append("and a.publish_date < '" + WzDateUtil.dt2Str(new Date(), WzDateUtil.DT_FMT_5) + "' ");
        return sql.toString();
    }

    public String selectByKeyStrOrRecommendProvider(ContentCondition cc) {
        StringBuilder sql = new StringBuilder("select DISTINCT ");
        sql.append("a.id id, ");
        sql.append("a.title title, ");
        sql.append("a.cover cover, ");
        sql.append("a.categroy_id categroy_id, ");
        sql.append("b.name category_name, ");
        sql.append("b.code category_code, ");
        sql.append("a.type_id type_id, ");
        sql.append("a.only_url only_url, ");
        sql.append("a.content_url content_url, ");
        sql.append("a.publish_date publish_date, ");
        sql.append("a.clicks clicks, ");
        sql.append("case a.stick_sort when 0 then a.update_time else a.publish_date end as sort_date, ");
        if (cc.isLoadAll()) {
            sql.append("a.copied copied, ");
            sql.append("a.copy_from copy_from, ");
            sql.append("a.copy_from_url copy_from_url, ");
            sql.append("a.author author, ");
            sql.append("a.editor editor, ");
            sql.append("a.publish_now publish_now, ");
            sql.append("a.publish_user_id publish_user_id, ");
            sql.append("a.check_date check_date, ");
            sql.append("a.check_user_id check_user_id, ");
            sql.append("a.check_status check_status, ");
            sql.append("a.readable readable, ");
            sql.append("a.stick_sort stick_sort, ");
            sql.append("a.recommend recommend, ");
            sql.append("a.creater creater, ");
            sql.append("a.create_time create_time, ");
            sql.append("a.updater updater, ");
            sql.append("a.update_time update_time, ");
            sql.append("a.tags tags, ");
            sql.append("a.content_txt content_txt, ");
            sql.append("c.file_name file_name, ");
            sql.append("c.save_path save_path, ");
            sql.append("c.req_rel_url req_rel_url, ");
            sql.append("c.id file_id, ");
        }
        sql.append(" g.info info,g.checker checker,g.check_time check_time, ");
        sql.append("a.description description ");
        sql.append(" from cms_content a inner join cms_category b on a.categroy_id = b.id ");
        if (cc.isLoadAll()) {
            sql.append(" left join cms_file c on c.owner_type = 0 and c.owner_id = a.id ");
        }
        sql.append(" left join (select * from `bus_user_form_check` x where check_time =(select max(check_time) from `bus_user_form_check` y where x.form_id = y.form_id)) g on a.id = g.form_id ");
        sql.append(" where 1 = 1 ");
        if (null != cc.getCheckStatus()) {
            sql.append(" and a.check_status = " + cc.getCheckStatus() + " ");
        }
        if (null != cc.getReadable()) {
            sql.append(" and a.readable = " + cc.getReadable() + " ");
        }
        if (null != cc.getRecommend()) {
            sql.append(" and a.recommend = " + cc.getRecommend() + " ");
        }
        if (WzStrUtil.isNotBlank(cc.getKeyStr())) {
            sql.append(" and ( ");
            sql.append(" a.title like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or a.description like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or a.author like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or a.tags like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or a.content_txt like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or b.name like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or b.tags like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or b.memo like '%" + cc.getKeyStr() + "%' ");
            sql.append(" ) ");
        }
        sql.append(" and a.type_id = 1 ");
        sql.append(" and a.publish_date < '" + WzDateUtil.dt2Str(new Date(), WzDateUtil.DT_FMT_5) + "' ");
        sql.append("order by a.stick_sort, sort_date desc, a.id asc ");
//        sql.append(" order by a.stick_sort, a.update_time desc, a.publish_date desc, a.id asc ");
        if (cc.getNeedPaging()) {
            sql.append(" limit " + cc.getPageBegin() + ", " + cc.getPageSize());
        }
        return sql.toString();
    }

    public String selectByCategoryCodeProvider(ContentCondition cc) {
        StringBuilder sql = new StringBuilder("select DISTINCT ");
        sql.append("a.id id, ");
        sql.append("a.title title, ");
        sql.append("a.cover cover, ");
        sql.append("a.categroy_id categroy_id, ");
        sql.append("b.name category_name, ");
        sql.append("b.code category_code, ");
        sql.append("a.type_id type_id, ");
        sql.append("a.only_url only_url, ");
        sql.append("a.content_url content_url, ");
        sql.append("a.publish_date publish_date, ");
        sql.append("a.clicks clicks, ");
        sql.append("case a.stick_sort when 0 then a.update_time else a.publish_date end as sort_date, ");
        if (cc.isLoadAll()) {
            sql.append("a.copied copied, ");
            sql.append("a.copy_from copy_from, ");
            sql.append("a.copy_from_url copy_from_url, ");
            sql.append("a.author author, ");
            sql.append("a.editor editor, ");
            sql.append("a.publish_now publish_now, ");
            sql.append("a.publish_user_id publish_user_id, ");
            sql.append("a.check_date check_date, ");
            sql.append("a.check_user_id check_user_id, ");
            sql.append("a.check_status check_status, ");
            sql.append("a.readable readable, ");
            sql.append("a.stick_sort stick_sort, ");
            sql.append("a.recommend recommend, ");
            sql.append("a.creater creater, ");
            sql.append("a.create_time create_time, ");
            sql.append("a.updater updater, ");
            sql.append("a.update_time update_time, ");
            sql.append("a.tags tags, ");
            sql.append("a.content_txt content_txt, ");
            sql.append("c.file_name file_name, ");
            sql.append("c.save_path save_path, ");
            sql.append("c.req_rel_url req_rel_url, ");
            sql.append("c.id file_id, ");
        }
        sql.append(" g.info info,g.checker checker,g.check_time check_time, ");
        sql.append("a.description description ");
        sql.append("from cms_content a inner join cms_category b on a.categroy_id = b.id ");
        if (cc.isLoadAll()) {
            sql.append(" left join cms_file c on c.owner_type = 0 and c.owner_id = a.id ");
        }
        sql.append(" left join (select * from `bus_user_form_check` x where check_time =(select max(check_time) from `bus_user_form_check` y where x.form_id = y.form_id)) g on a.id = g.form_id ");
        sql.append("where 1 = 1 ");
        if (WzStrUtil.isNotBlank(cc.getCategoryCode())) {
            sql.append("and b.code = '" + cc.getCategoryCode() + "' ");
        }
        if (null != cc.getCheckStatus()) {
            sql.append("and a.check_status = " + cc.getCheckStatus() + " ");
        }
        if (null != cc.getReadable()) {
            sql.append("and a.readable = " + cc.getReadable() + " ");
        }
        if (null != cc.getRecommend()) {
            sql.append("and a.recommend = " + cc.getRecommend() + " ");
        }
        if (WzStrUtil.isNotBlank(cc.getKeyStr())) {
            sql.append(" and ( ");
            sql.append(" a.title like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or a.description like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or a.author like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or a.tags like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or a.content_txt like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or b.name like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or b.tags like '%" + cc.getKeyStr() + "%' ");
            sql.append(" or b.memo like '%" + cc.getKeyStr() + "%' ");
            sql.append(" ) ");
        }
        sql.append("and a.publish_date < '" + WzDateUtil.dt2Str(new Date(), WzDateUtil.DT_FMT_5) + "' ");
        sql.append("order by a.stick_sort, sort_date desc, a.id asc ");
//        sql.append("order by a.stick_sort, a.update_time desc, a.publish_date desc, a.id asc ");
        if (cc.getNeedPaging()) {
            sql.append("limit " + cc.getPageBegin() + ", " + cc.getPageSize());
        }
        return sql.toString();
    }

    public String selectByPrimaryKeyProvider(String id) {
        StringBuilder sql = new StringBuilder("select DISTINCT ");
        sql.append("a.id id, ");
        sql.append("a.title title, ");
        sql.append("a.cover cover, ");
        sql.append("a.only_url only_url, ");
        sql.append("a.content_url content_url, ");
        sql.append("a.publish_date publish_date, ");
        sql.append("a.clicks clicks, ");
        sql.append("a.categroy_id categroy_id, ");
        sql.append("b.name category_name, ");
        sql.append("b.code category_code, ");
        sql.append("a.type_id type_id, ");
        sql.append("a.copied copied, ");
        sql.append("a.copy_from copy_from, ");
        sql.append("a.copy_from_url copy_from_url, ");
        sql.append("a.author author, ");
        sql.append("a.editor editor, ");
        sql.append("a.publish_now publish_now, ");
        sql.append("a.publish_user_id publish_user_id, ");
        sql.append("a.check_date check_date, ");
        sql.append("a.check_user_id check_user_id, ");
        sql.append("a.check_status check_status, ");
        sql.append("a.readable readable, ");
        sql.append("a.stick_sort stick_sort, ");
        sql.append("a.recommend recommend, ");
        sql.append("a.creater creater, ");
        sql.append("a.create_time create_time, ");
        sql.append("a.updater updater, ");
        sql.append("a.update_time update_time, ");
        sql.append("a.tags tags, ");
        sql.append("a.content_txt content_txt, ");
        sql.append("c.file_name file_name, ");
        sql.append("c.save_path save_path, ");
        sql.append("c.req_rel_url req_rel_url, ");
        sql.append("c.id file_id, ");
        sql.append("a.description description ");
        sql.append("from cms_content a inner join cms_category b on a.categroy_id = b.id ");
        sql.append("left join cms_file c on c.owner_type = 0 and c.owner_id = a.id ");
        sql.append("where 1 = 1 ");
        sql.append("and a.id = '" + id + "' ");
        sql.append("and a.check_status = 1 ");
        sql.append("and a.readable = 1 ");
        return sql.toString();
    }
    
    public String findByCondition(VoQueryCms query){
    	StringBuilder sql = new StringBuilder("select a.id, a.title, b.name as categroyId, a.type_id as typeId, ")
    	.append("u.user_name as publishUserId, a.publish_date as publishDate, a.check_date as checkDate, ")
    	.append("v.user_name as checkUserId, a.clicks, a.stick_sort as stickSort, a.recommend, a.check_status as checkStatus, a.readable, ")
    	.append("a.creater, a.updater, a.create_time as createTime, a.update_time as updateTime, ")
        .append(" g.info info,g.checker checker,g.check_time check_time ");
    	sql.append("from `cms_content` a left join `cms_category` b on a.categroy_id = b.id ")
    	.append("left join `sys_user` u on a.publish_user_id = u.user_id ")
    	.append("left join `sys_user` v on a.check_user_id = v.user_id ")
        .append(" left join (select * from `bus_user_form_check` x where check_time =(select max(check_time) from `bus_user_form_check` y where x.form_id = y.form_id)) g on a.id = g.form_id ");
    	//sql.append("left join `cms_file` f on a.id = f.owner_id ");
    	sql.append("where 1=1");
    	if(query != null) {
            if(!StringUtils.isEmpty(query.getPublishUserId())) {
                sql.append(" and a.publish_user_id = '").append(query.getPublishUserId()).append("' ");
            }
    		if(!StringUtils.isEmpty(query.getReadable())) {
    			sql.append(" and a.readable = ").append(query.getReadable());
    		}
    		if(!StringUtils.isEmpty(query.getCheckStatus())) {
    			sql.append(" and a.check_status = ").append(query.getCheckStatus());
    		}
    		if(!StringUtils.isEmpty(query.getType())) {
    			sql.append(" and a.type_id = ").append(query.getType());
    		}
    		if(!StringUtils.isEmpty(query.getPublishStart())) {
    			sql.append(" and a.publish_date >= '").append(query.getPublishStart()).append("' ");
    		}
    		if(!StringUtils.isEmpty(query.getPublishEnd())) {
    			sql.append(" and a.publish_date <= '").append(query.getPublishEnd()).append("' ");
    		}
    		if(!StringUtils.isEmpty(query.getObjName())) {
                sql.append(" and ( ");
                sql.append(" a.title like '%" + query.getObjName() + "%' ");
                sql.append(" or a.description like '%" + query.getObjName() + "%' ");
                sql.append(" or a.author like '%" + query.getObjName() + "%' ");
                sql.append(" or a.tags like '%" + query.getObjName() + "%' ");
                sql.append(" or a.content_txt like '%" + query.getObjName() + "%' ");
                sql.append(" or b.name like '%").append(query.getObjName()).append("%' ");
                sql.append(" or u.user_name like '%").append(query.getObjName()).append("%' ");
                sql.append(" or v.user_name like '%").append(query.getObjName()).append("%' ");
                sql.append(" or a.creater like '%").append(query.getObjName()).append("%' ");
                sql.append(" ) ");
    		}
    	}
//    	System.out.println(sql.toString());
    	return sql.toString();
    }
    
    public String update(CmsContent e){
    	SQL sql = new SQL();
    	sql.UPDATE("`cms_content`");
    	if(e.getTitle() != null)
    		sql.SET("title = #{title,jdbcType=VARCHAR}");
    	if(e.getCategroyId() != null)
    		sql.SET("categroy_id = #{categroyId,jdbcType=VARCHAR}");
    	if(e.getTypeId() != null)
    		sql.SET("type_id = #{typeId,jdbcType=INTEGER}");
    	if(e.getCover() != null)
    		sql.SET("cover = #{cover,jdbcType=VARCHAR}");
    	if(e.getCopied() != null)
    		sql.SET("copied = #{copied,jdbcType=INTEGER}");
    	if(e.getCopyFrom() != null)
    		sql.SET("copy_from = #{copyFrom,jdbcType=VARCHAR}");
    	if(e.getCopyFromUrl() != null)
    		sql.SET("copy_from_url = #{copyFromUrl,jdbcType=VARCHAR}");
    	if(e.getAuthor() != null)
    		sql.SET("author = #{author,jdbcType=VARCHAR}");
    	if(e.getEditor() != null)
    		sql.SET("editor = #{editor,jdbcType=VARCHAR}");
    	if(e.getTags() != null)
    		sql.SET("tags = #{tags,jdbcType=VARCHAR}");
    	if(e.getDescription() != null)
    		sql.SET("description = #{description,jdbcType=VARCHAR}");
    	if(e.getOnlyUrl() != null)
    		sql.SET("only_url = #{onlyUrl,jdbcType=INTEGER}");
    	if(e.getContentUrl() != null)
    		sql.SET("content_url = #{contentUrl,jdbcType=VARCHAR}");
    	if(e.getPublishNow() != null)
    		sql.SET("publish_now = #{publishNow,jdbcType=INTEGER}");
    	if(e.getPublishDate() != null)
    		sql.SET("publish_date = #{publishDate,jdbcType=DATE}");
    	if(e.getPublishUserId() != null)
    		sql.SET("publish_user_id = #{publishUserId,jdbcType=VARCHAR}");
    	if(e.getCheckDate() != null)
    		sql.SET("check_date = #{checkDate,jdbcType=VARCHAR}");
    	if(e.getCheckUserId() != null)
    		sql.SET("check_user_id = #{checkUserId,jdbcType=VARCHAR}");
    	if(e.getContentTxt()!= null)
    		sql.SET("content_txt = #{contentTxt,jdbcType=VARCHAR}");
    	if(e.getClicks() != null)
    		sql.SET("clicks = #{clicks,jdbcType=INTEGER}");
    	if(e.getStickSort() != null)
    		sql.SET("stick_sort = #{stickSort,jdbcType=INTEGER}");
    	if(e.getRecommend() != null)
    		sql.SET("recommend = #{recommend,jdbcType=INTEGER}");
    	if(e.getCheckStatus() != null)
    		sql.SET("check_status = #{checkStatus,jdbcType=INTEGER}");
    	if(e.getReadable() != null)
    		sql.SET("readable = #{readable,jdbcType=INTEGER}");
    	if(e.getUpdater() != null)
    		sql.SET("updater = #{updater,jdbcType=VARCHAR}");
    	sql.WHERE("id = #{id,jdbcType=VARCHAR}");
    	return sql.toString();
    }
}
